package com.ncloud.crm.mapper;

import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.ncloud.crm.entity.BsNode;
import com.ncloud.crm.entity.order.OrderNode;

/**
 * Created by shiqiang on 2017/4/17.
 */
@Mapper
public interface BsNodeMapper {
	@Select(value = { "select * from bs_node where node_id = #{nodeId} and  now() between begin_date and end_date" })
	@Results({ @Result(column = "prod_inst_id", property = "prodInstId"),
			@Result(column = "prod_id", property = "prodId"), @Result(column = "node_id", property = "nodeId"),
			@Result(column = "node_name", property = "nodeName"), @Result(column = "node_type", property = "nodeType"),
			@Result(column = "access_type", property = "accessType"),
			@Result(column = "eff_flag", property = "effFlag"), @Result(column = "begin_date", property = "beginDate"),
			@Result(column = "end_date", property = "endDate"),
			@Result(column = "update_time", property = "updateTime"),
			@Result(column = "update_user", property = "updateUser"),
			@Result(column = "node_sub_type", property = "nodeSubType"),
			@Result(column = "cust_id", property = "custId"),
			@Result(column = "discnt_inst_id", property = "discntInstId") })
	public BsNode getNodeInfoByPk(Long nodeId);

	@Select(value = { "select * from bs_node where node_id = #{nodeId} and now() between begin_date and end_date" })
	@Results({ @Result(column = "node_inst_id", property = "nodeInstId"),
			@Result(column = "prod_inst_id", property = "prodInstId"), @Result(column = "node_id", property = "nodeId"),
			@Result(column = "node_name", property = "nodeName"), @Result(column = "node_type", property = "nodeType"),
			@Result(column = "access_type", property = "accessType"),
			@Result(column = "eff_flag", property = "effFlag"), @Result(column = "begin_date", property = "beginDate"),
			@Result(column = "end_date", property = "endDate"),
			@Result(column = "update_time", property = "updateTime"),
			@Result(column = "update_user", property = "updateUser"),
			@Result(column = "node_sub_type", property = "nodeSubType"),
			@Result(column = "cust_id", property = "custId"), @Result(column = "prod_id", property = "prodId"),
			@Result(column = "discnt_inst_id", property = "discntInstId") })
	public BsNode getNodeInfoByNodeId(Long nodeId);

	@Select(value = { "select * from bs_o_node where node_id = #{nodeId} and now() between begin_date and end_date" })
	@Results({ @Result(column = "work_id", property = "workId"), @Result(column = "order_id", property = "orderId"),
			@Result(column = "modify_tag", property = "modifyTag"), @Result(column = "cust_id", property = "custId"),
			@Result(column = "prod_inst_id", property = "prodInstId"),
			@Result(column = "discnt_inst_id", property = "discntInstId"),
			@Result(column = "prod_id", property = "prodId"), @Result(column = "node_id", property = "nodeId"),
			@Result(column = "node_name", property = "nodeName"), @Result(column = "node_type", property = "nodeType"),
			@Result(column = "node_sub_type", property = "nodeSubType"),
			@Result(column = "access_type", property = "accessType"),
			@Result(column = "eff_flag", property = "effFlag"), @Result(column = "begin_date", property = "beginDate"),
			@Result(column = "end_date", property = "endDate"),
			@Result(column = "update_time", property = "updateTime"),
			@Result(column = "update_user", property = "updateUser"),
			@Result(column = "discnt_id", property = "discntId") })
	public BsNode getBsNodeInfoByNodeId(Long nodeId);

	@Insert("insert into bs_node(prod_inst_id,cust_id, node_id, node_name, node_type,node_sub_type, access_type, eff_flag, begin_date, end_date, update_time, update_user,prod_id,discnt_inst_id)"
			+ " values(#{prodInstId},#{custId}, #{nodeId}, #{nodeName}, #{nodeType},#{nodeSubType}, #{accessType}, #{effFlag}, #{beginDate}, #{endDate}, #{updateTime}, #{updateUser}, #{prodId}, #{discntInstId})")
	public int insertNode(BsNode node);

	@Update("update bs_node set node_name=#{nodeName}, node_type=#{nodeType}, access_type=#{accessType}, eff_flag=#{effFlag},"
			+ " end_date=#{endDate}, update_time=#{updateTime}, update_user=#{updateUser} where node_id=#{nodeId} and begin_date=#{beginDate}")
	public int updateNode(BsNode node);

	// 找项目下所有节点列表
	@Select(value = { "select c.* from bs_node c left join bs_proj_prod b on b.prod_inst_id = "
			+ " c.prod_inst_id left join bs_project a on a.project_id = b.project_id"
			+ " where a.project_id = #{projectId} and a.manager = #{manage} "
			+ " and now() between b.begin_date and b.end_date" + " and now() between c.begin_date and c.end_date" })
	@Results({ @Result(column = "node_id", property = "nodeId"), @Result(column = "node_name", property = "nodeName"),
			@Result(column = "node_type", property = "nodeType"),
			@Result(column = "access_type", property = "accessType"),
			@Result(column = "eff_flag", property = "effFlag"), @Result(column = "begin_date", property = "beginDate"),
			@Result(column = "end_date", property = "endDate"),
			@Result(column = "update_time", property = "updateTime"),
			@Result(column = "update_user", property = "updateUser"),
			@Result(column = "node_sub_type", property = "nodeSubType"),
			@Result(column = "cust_id", property = "custId"), @Result(column = "prod_id", property = "prodId"),
			@Result(column = "discnt_inst_id", property = "discntInstId")

	})
	public List<BsNode> getBsNodes(@Param("projectId") String projectId, @Param("manage") String manage);

	/* 查询项目下的节点信息 */
	@Select(value = { "select b.*,f_get_node_item(b.node_id,'bandWidth') as bandWidth,f_get_node_area(b.node_id) as areaName,"
			+ "f_get_node_item(b.node_id,'nodeAccount') as nodeAccount,f_get_node_item(b.node_id,'nodeAcctPwd') as nodeAcctPwd"
			+ " from bs_cust a ,bs_node b where a.cust_id = b.cust_id and b.eff_flag = '1' "
			+ " and now() BETWEEN b.begin_date and b.end_date and a.cust_id = #{1}" })
	@Results({ @Result(column = "node_id", property = "nodeId"), @Result(column = "node_name", property = "nodeName"),
			@Result(column = "node_type", property = "nodeType"),@Result(column = "bandWidth", property = "bandWidth"),
			@Result(column = "access_type", property = "accessType"),
			@Result(column = "eff_flag", property = "effFlag"), @Result(column = "begin_date", property = "beginDate"),
			@Result(column = "end_date", property = "endDate"),@Result(column = "areaName", property = "areaName"),
			@Result(column = "update_time", property = "updateTime"),@Result(column = "nodeAccount", property = "nodeAccount"),
			@Result(column = "update_user", property = "updateUser"),@Result(column = "nodeAcctPwd", property = "nodeAcctPwd"),
			@Result(column = "node_sub_type", property = "nodeSubType"),
			@Result(column = "cust_id", property = "custId"), @Result(column = "prod_id", property = "prodId"),
			@Result(column = "discnt_inst_id", property = "discntInstId") })
	public List<BsNode> getBsNodesByCust(Long custId);

	// 查询订单下的节点信息
	@Select(value = { "SELECT a.*,b.work_id,b.node_id,b.order_id,b.work_state,b.construct_item,b.construct_user,b.name "
			+ ",b.email,b.mobile,b.telephone,b.construct_time,b.finish_time FROM (SELECT order_id,cust_id,cust_name , "
			+ "prod_id,order_type,order_state,create_date,channel_id, create_user FROM bs_o_order WHERE order_id =#{0} "
			+ "UNION SELECT order_id,cust_id,cust_name, prod_id,order_type,order_state,create_date,channel_id,create_user FROM bs_oh_order "
			+ "WHERE order_id =#{0}) a ,(SELECT b.*,a.name,a.email,a.mobile,a.telephone  FROM bs_o_work b,sys_user a WHERE b.construct_user=a.id  "
			+ " and b.order_id=#{0} UNION SELECT b.*,'' AS name,'' AS email,'' AS mobile,'' AS telephone FROM  "
			+ "bs_o_work b WHERE b.work_state ='1' and b.order_id =#{0} UNION SELECT b.*,a.name,a.email,a.mobile,a.telephone "
			+ "FROM bs_oh_work b,sys_user a WHERE b.construct_user=a.id AND b.order_id =#{0} ) b "
            + " WHERE a.order_id=b.order_id AND a.order_id =#{0}" })
	@Results({ @Result(column = "order_id", property = "orderId"), @Result(column = "cust_id", property = "custId"),
			@Result(column = "cust_name", property = "custName"), @Result(column = "prod_id", property = "prodId"),
			@Result(column = "name", property = "name"), @Result(column = "email", property = "email"),
			@Result(column = "mobile", property = "mobile"), @Result(column = "telephone", property = "telephone"),
			@Result(column = "order_type", property = "orderType"),
			@Result(column = "order_state", property = "orderState"),
			@Result(column = "create_date", property = "createDate"),
			@Result(column = "channel_id", property = "channelId"),
			@Result(column = "create_user", property = "createUser"), @Result(column = "work_id", property = "workId"),
			@Result(column = "node_id", property = "nodeId"), @Result(column = "work_state", property = "workState"),
			@Result(column = "construct_item", property = "constructItem"),
			@Result(column = "construct_user", property = "constructUser"),
			@Result(column = "construct_time", property = "constructTime"),
			@Result(column = "finish_time", property = "finishTime") })
	public List<OrderNode> getBsNodesByOrder(Long orderId);

	/*根据节点信息获取经纬度地址信息*/
	@Select(value = { "select a.node_id,a.node_name, c.attr_value as nodeCode,b.uses_address,b.longitude,b.latitude from bs_node a,bs_eqpt_info b ,bs_node_item c where " +
			"a.node_id =b.node_id and a.node_id = c.node_id and now() BETWEEN a.begin_date and a.end_date and  c.attr_code='applianceName' " +
			"and c.attr_value = #{0} and a.cust_id= #{1}" })
	@Results({ @Result(javaType=String.class,column = "node_id", property = "nodeId"), @Result(column = "node_name", property = "nodeName"),
			@Result(column = "nodeCode", property = "nodeCode"),@Result(column = "uses_address", property = "address"),
			@Result(column = "longitude", property = "lng"),
			@Result(column = "latitude", property = "lat") })
	public Map<String,Object> getBsNodeByCode(String nodeCode, Long custId);

}
